<?php
/**
 * 通勤通学時間検索用SQL作成クラス<br />
 *
 * @package apamanshop
 * @author SS鈴木
 * @copyright
 */
class Rstobi_Model_TsukintsugakujikanSearch extends Rstobi_Model_Base
{
	/**
	 * 通勤通学時間検索用SQL取得
	 *
	 * @access	public
	 * @param   array
	 * @param	array ※参照渡し
	 * @return  string
	 */
	public function getFromWhereForTsukintsugakujikansearch($params,&$db_params)
	{
		
		// データ格納
		$db_params['fromTime']      = $params['EndRangeTimeFrom'];
		$db_params['toTime']        = $params['EndRangeTimeTo'];
		$db_params['transferCount'] = $params['TransferCount'];
		$db_params['stCd']          = $params['TargetEkiCd'];
		
		foreach($params['RailCd'] as $key => $val){
			$db_params['railCd' . $key] = $val;
		}
		
		$sql .="     FROM";
		$sql .="         (SELECT";
		$sql .="             T94_ENSEN_CD,";
		$sql .="             T94_EKI_CD,";
		$sql .="             T94_ROSENZU_EKI_CD,";
		$sql .="             T94_TARGET_EKI_CD,";
		$sql .="             MIN(T94_TARGET_EKI_TIME) AS T94_TARGET_EKI_TIME";
		$sql .="         FROM";
		$sql .="             (SELECT";
		$sql .="                 T94_ENSEN_CD,";
		$sql .="                 T94_EKI_CD,";
		$sql .="                 T94_TARGET_EKI_TIME,";
		$sql .="                 T94_ROSENZU_EKI_CD,";
		$sql .="                 T94_TARGET_EKI_CD";
		$sql .="             FROM";
		$sql .="                 T94COMMUTING_EKI_T03SUM T94";
		$sql .="             WHERE";
		$sql .="                 T94_TARGET_EKI_CD = :stCd AND ";
		$sql .="                 T94_TARGET_EKI_TIME BETWEEN :fromTime AND :toTime AND";
		$sql .="                 T94_TRANSFER_COUNT = :transferCount";
		$sql .="    	            AND T94_ENSEN_CD IN ( ";
				    	                 $sepa = "";
				    	                 foreach($params['RailCd'] as $key => $val){
				    	                 	$sql .= $sepa . "  :railCd" . $key;
				    	                 	$sepa = ",";
				    	                 }
		$sql .="		    	    )";
		$sql .="             UNION ";
		$sql .="             SELECT";
		$sql .="                 M03_ENSEN_CD AS T94_ENSEN_CD,";
		$sql .="                 M03_EKI_CD AS T94_EKI_CD,";
		$sql .="                 T94_TARGET_EKI_TIME,";
		$sql .="                 T94_ROSENZU_EKI_CD,";
		$sql .="                 T94_TARGET_EKI_CD";
		$sql .="             FROM";
		$sql .="                 (SELECT";
		$sql .="                     T94_RENRAKU_EKI_CD ,";
		$sql .="                     T94_TARGET_EKI_TIME,";
		$sql .="                     T94_TARGET_EKI_CD,";
		$sql .="                     T94_ROSENZU_EKI_CD";
		$sql .="                 FROM";
		$sql .="                     T94COMMUTING_EKI_T03SUM T94";
		$sql .="                 WHERE";
		$sql .="                     T94_TARGET_EKI_CD = :stCd AND ";
		$sql .="                     T94_TARGET_EKI_TIME BETWEEN :fromTime AND :toTime AND";
		$sql .="                     T94_TRANSFER_COUNT = :transferCount";
		$sql .="    	            AND T94_ENSEN_CD IN ( ";
				    	                 $sepa = "";
				    	                 foreach($params['RailCd'] as $key => $val){
				    	                 	$sql .= $sepa . "  :railCd" . $key;
				    	                 	$sepa = ",";
				    	                 }
		$sql .="		    	       )";
		$sql .="                   ) WORK";
		$sql .="                  ,M03EKI M03";
		$sql .="              WHERE";
		$sql .="                  WORK.T94_RENRAKU_EKI_CD = M03.M03_RENRAKU_EKI_CD";
		$sql .="              )";
		$sql .="          GROUP BY";
		$sql .="             T94_ENSEN_CD,";
		$sql .="             T94_EKI_CD,";
		$sql .="             T94_ROSENZU_EKI_CD,";
		$sql .="             T94_TARGET_EKI_CD) T94";
		$sql .="         ,T03WEB_HEYA T03";
		$sql .="     WHERE";
		$sql .="         ( T94.T94_EKI_CD = T03_EKI_CD01 OR";
		$sql .="           T94.T94_EKI_CD = T03_EKI_CD02 OR";
		$sql .="           T94.T94_EKI_CD = T03_EKI_CD03 )";
		
		return $sql;
	}

	// FROM句のみ取得
	public function getFromForTsukintsugakujikansearch($params,&$db_params)
	{
		
		// データ格納
		$db_params['fromTime']      = $params['EndRangeTimeFrom'];
		$db_params['toTime']        = $params['EndRangeTimeTo'];
		$db_params['transferCount'] = $params['TransferCount'];
		$db_params['stCd']          = $params['TargetEkiCd'];
		
		foreach($params['RailCd'] as $key => $val){
			$db_params['railCd' . $key] = $val;
		}
		
		$sql .="     FROM";
		$sql .="         (SELECT";
		$sql .="             T94_ENSEN_CD,";
		$sql .="             T94_EKI_CD,";
		$sql .="             T94_ROSENZU_EKI_CD,";
		$sql .="             T94_TARGET_EKI_CD,";
		$sql .="             MIN(T94_TARGET_EKI_TIME) AS T94_TARGET_EKI_TIME";
		$sql .="         FROM";
		$sql .="             (SELECT";
		$sql .="                 T94_ENSEN_CD,";
		$sql .="                 T94_EKI_CD,";
		$sql .="                 T94_TARGET_EKI_TIME,";
		$sql .="                 T94_ROSENZU_EKI_CD,";
		$sql .="                 T94_TARGET_EKI_CD";
		$sql .="             FROM";
		$sql .="                 T94COMMUTING_EKI_T03SUM T94";
		$sql .="             WHERE";
		$sql .="                 T94_TARGET_EKI_CD = :stCd AND ";
		$sql .="                 T94_TARGET_EKI_TIME BETWEEN :fromTime AND :toTime AND";
		$sql .="                 T94_TRANSFER_COUNT <= :transferCount";
		$sql .="    	            AND T94_ENSEN_CD IN ( ";
				    	                 $sepa = "";
				    	                 foreach($params['RailCd'] as $key => $val){
				    	                 	$sql .= $sepa . "  :railCd" . $key;
				    	                 	$sepa = ",";
				    	                 }
		$sql .="		    	    )";
		$sql .="             UNION ";
		$sql .="             SELECT";
		$sql .="                 M03_ENSEN_CD AS T94_ENSEN_CD,";
		$sql .="                 M03_EKI_CD AS T94_EKI_CD,";
		$sql .="                 T94_TARGET_EKI_TIME,";
		$sql .="                 T94_ROSENZU_EKI_CD,";
		$sql .="                 T94_TARGET_EKI_CD";
		$sql .="             FROM";
		$sql .="                 (SELECT";
		$sql .="                     T94_RENRAKU_EKI_CD ,";
		$sql .="                     T94_TARGET_EKI_TIME,";
		$sql .="                     T94_TARGET_EKI_CD,";
		$sql .="                     T94_ROSENZU_EKI_CD";
		$sql .="                 FROM";
		$sql .="                     T94COMMUTING_EKI_T03SUM T94";
		$sql .="                 WHERE";
		$sql .="                     T94_TARGET_EKI_CD = :stCd AND ";
		$sql .="                     T94_TARGET_EKI_TIME BETWEEN :fromTime AND :toTime AND";
		$sql .="                     T94_TRANSFER_COUNT <= :transferCount";
		$sql .="    	            AND T94_ENSEN_CD IN ( ";
				    	                 $sepa = "";
				    	                 foreach($params['RailCd'] as $key => $val){
				    	                 	$sql .= $sepa . "  :railCd" . $key;
				    	                 	$sepa = ",";
				    	                 }
		$sql .="		    	       )";
		$sql .="                   ) WORK";
		$sql .="                  ,M03EKI M03";
		$sql .="              WHERE";
		$sql .="                  WORK.T94_RENRAKU_EKI_CD = M03.M03_RENRAKU_EKI_CD";
		$sql .="              )";
		$sql .="          GROUP BY";
		$sql .="             T94_ENSEN_CD,";
		$sql .="             T94_EKI_CD,";
		$sql .="             T94_ROSENZU_EKI_CD,";
		$sql .="             T94_TARGET_EKI_CD) T94";
		$sql .="         ,T03WEB_HEYA T03";
		
		return $sql;
	}

	// Where句のみ取得
	public function getWhereForTsukintsugakujikansearch($params,&$db_params)
	{
		
		$sql .="     WHERE";
		$sql .="         ( T94.T94_EKI_CD = T03_EKI_CD01 OR";
		$sql .="           T94.T94_EKI_CD = T03_EKI_CD02 OR";
		$sql .="           T94.T94_EKI_CD = T03_EKI_CD03 )";
		
		return $sql;
	}
}
?>